const router = require('express').Router()
const {db,jwtRouter} = require('../util')

router.get('/', (req,res)=>{
    let sql = 'SELECT * FROM ti_categories'
    db.query(sql, (err,results)=>{
        res.json({
            ok: 1,
            data: results
        })
    })
})


router.get('/:id(\\d+)', (req,res)=>{
    let sql = 'SELECT * FROM ti_categories WHERE id = ?'
    db.query(sql, req.params.id, (err,results)=>{
        res.json({
            ok: 1,
            data: results[0]
        })
    })
    
})

jwtRouter.get('/:id(\\d+)/questions', (req,res)=>{
    let type = req.query.type || 'all'
    if(type == 'wrong') {
        let sql = `SELECT * 
                    FROM ti_user_questions a
                    LEFT JOIN ti_questions b ON a.question_id=b.id
                     WHERE b.category_id = ? AND a.user_id=? AND a.isright=0`
        db.query(sql, [req.params.id, req.user.id], (err,results)=>{
            res.json({
                ok: 1,
                data: results
            })
        })
    } else if(type=='right') {
        let sql = `SELECT * 
                    FROM ti_user_questions a
                    LEFT JOIN ti_questions b ON a.question_id=b.id
                     WHERE b.category_id = ? AND a.user_id=? AND a.isright=1`
        db.query(sql, [req.params.id, req.user.id], (err,results)=>{
            res.json({
                ok: 1,
                data: results
            })
        })
    }else if(type=='undo') {
        let sql = `SELECT * 
                    FROM ti_questions
                     WHERE category_id=? 
                     AND id NOT IN(SELECT question_id FROM ti_user_questions WHERE user_id=?)`
        db.query(sql, [req.params.id, req.user.id], (err,results)=>{
            res.json({
                ok: 1,
                data: results
            })
        })
    } else {
        let sql = 'SELECT * FROM ti_questions WHERE category_id = ?'
        db.query(sql, req.params.id, (err,results)=>{
            res.json({
                ok: 1,
                data: results
            })
        })
    }
})

jwtRouter.get('/:id/question_count_info', (req,res)=>{
    let sql = 'SELECT COUNT(*) total FROM ti_questions WHERE category_id=?'
    db.query(sql, req.params.id, (err,results)=>{
        let total = results[0].total
        sql = `SELECT COUNT(*) right_count
                FROM ti_user_questions a
                LEFT JOIN ti_questions b ON a.question_id=b.id
                WHERE b.category_id=? AND a.isright=1 AND a.user_id=?`
        db.query(sql, [req.params.id, req.user.id], (err,results)=>{
            let right = results[0].right_count
            sql = `SELECT COUNT(*) wrong
                FROM ti_user_questions a
                LEFT JOIN ti_questions b ON a.question_id=b.id
                WHERE b.category_id=? AND a.isright=0 AND a.user_id=?`
            db.query(sql, [req.params.id, req.user.id], (err,results)=>{
                res.json({
                    ok: 1,
                    data: {
                        total,
                        right,
                        wrong: results[0].wrong,
                        undo: total-right-results[0].wrong
                    }
                })
            })
        })
    })
})

module.exports = [router, jwtRouter]